package cn.com.chec.en.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.chec.en.model.domain.ChannelInfo;
import cn.com.chec.en.model.domain.NewsChannelRel;
import cn.com.chec.en.model.domain.NewsInfo;
import cn.com.chec.en.model.domain.QueryNewsObject;
import cn.com.chec.en.model.dto.NewsDTO;

@Repository
public class NewsInfoDao {
	protected static final Logger log = Logger.getLogger(NewsInfoDao.class);

	public List<NewsDTO> getNewsListByQueryPChannelId(QueryNewsObject query,
			int channelId) {
		List<NewsDTO> list = new ArrayList<NewsDTO>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT * FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk  ";

			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ? ";
			}
			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}

			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}

			List<ChannelInfo> channelList = new ArrayList<ChannelInfo>();
			List<Integer> childrenIds = new ArrayList<Integer>();
			if (channelId > 0) {
				ChannelInfoDao channelDao = new ChannelInfoDao();
				ChannelInfo channelInfo = channelDao
						.getChannelByChannelId(channelId);
				channelList.add(channelInfo);
				channelList.addAll(channelDao.getChildrenByChannelId(
						query.getLanguageId(), channelId));
				for (ChannelInfo ciInfo : channelList) {
					childrenIds.add(Integer.valueOf(ciInfo.getChannelId()));
				}
			}
			String channelParams = "";
			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (int num = 0; num < childrenIds.size(); num++) {
					channelParams = channelParams + ",?";
				}
				channelParams = channelParams.length() > 0 ? channelParams
						.substring(1) : "";
				sql = sql + " and rel.channelid_fk in (" + channelParams + ") ";
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}

			String order = "";
			switch (query.getOrderBy()) {
			case 1:
				order = "news.news_order";
				break;
			default:
				order = "news.news_publishtime";
			}

			sql = sql + " order by " + order + " desc limit ? ,? ";

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}

			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (Iterator<Integer> localIterator2 = childrenIds.iterator(); localIterator2
						.hasNext();) {
					int id = localIterator2.next() ;
					i++;
					pstmt.setInt(i, Integer.valueOf(id).intValue());
				}
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			i++;
			pstmt.setInt(i, query.getStart());
			i++;
			pstmt.setInt(i, query.getNum());
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list = getNewsDTOByRS(rs);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getNewsCountByQueryPChannelId(QueryNewsObject query,
			int channelId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT count(1) as count FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk  ";

			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ? ";
			}
			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}
			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}

			List<ChannelInfo> channelList = new ArrayList<ChannelInfo>();
			List<Integer> childrenIds = new ArrayList<Integer>();
			if (channelId > 0) {
				ChannelInfoDao channelDao = new ChannelInfoDao();
				ChannelInfo channelInfo = channelDao
						.getChannelByChannelId(channelId);
				channelList.add(channelInfo);
				channelList.addAll(channelDao.getChildrenByChannelId(
						query.getLanguageId(), channelId));
				for (ChannelInfo ciInfo : channelList) {
					childrenIds.add(Integer.valueOf(ciInfo.getChannelId()));
				}
			}
			String channelParams = "";
			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (int num = 0; num < childrenIds.size(); num++) {
					channelParams = channelParams + ",?";
				}
				channelParams = channelParams.length() > 0 ? channelParams
						.substring(1) : "";
				sql = sql + " and rel.channelid_fk in (" + channelParams + ") ";
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}

			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (Iterator<Integer> localIterator2 = childrenIds.iterator(); localIterator2
						.hasNext();) {
					int id = localIterator2.next();
					i++;
					pstmt.setInt(i, Integer.valueOf(id).intValue());
				}
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public List<NewsDTO> getNewsListByQueryPChannelIdDistinct(
			QueryNewsObject query, int channelId) {
		List<NewsDTO> list = new ArrayList<NewsDTO>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT * FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk  ";

			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}

			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}

			List<ChannelInfo> channelList = new ArrayList<ChannelInfo>();
			List<Integer> childrenIds = new ArrayList<Integer>();
			if (channelId > 0) {
				ChannelInfoDao channelDao = new ChannelInfoDao();
				ChannelInfo channelInfo = channelDao
						.getChannelByChannelId(channelId);
				channelList.add(channelInfo);
				channelList.addAll(channelDao.getChildrenByChannelId(
						query.getLanguageId(), channelId));
				for (ChannelInfo ciInfo : channelList) {
					childrenIds.add(Integer.valueOf(ciInfo.getChannelId()));
				}
			}
			String channelParams = "";
			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (int num = 0; num < childrenIds.size(); num++) {
					channelParams = channelParams + ",?";
				}
				channelParams = channelParams.length() > 0 ? channelParams
						.substring(1) : "";
				sql = sql + " and rel.channelid_fk in (" + channelParams + ") ";
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}
			sql = sql + " group by news.news_id ";

			String order = "";
			switch (query.getOrderBy()) {
			case 1:
				order = "news.news_order";
				break;
			default:
				order = "news.news_publishtime";
			}

			sql = sql + " order by " + order + " desc limit ? ,? ";

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}

			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (Iterator<Integer> localIterator2 = childrenIds.iterator(); localIterator2
						.hasNext();) {
					int id = localIterator2.next() ;
					i++;
					pstmt.setInt(i, Integer.valueOf(id).intValue());
				}
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			i++;
			pstmt.setInt(i, query.getStart());
			i++;
			pstmt.setInt(i, query.getNum());
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list = getNewsDTOByRS(rs);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getNewsCountByQueryPChannelIdDistinct(QueryNewsObject query,
			int channelId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT count(distinct(news.news_id)) as count FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk  ";

			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}
			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}

			List<ChannelInfo> channelList = new ArrayList<ChannelInfo>();
			List<Integer> childrenIds = new ArrayList<Integer>();
			if (channelId > 0) {
				ChannelInfoDao channelDao = new ChannelInfoDao();
				ChannelInfo channelInfo = channelDao
						.getChannelByChannelId(channelId);
				channelList.add(channelInfo);
				channelList.addAll(channelDao.getChildrenByChannelId(
						query.getLanguageId(), channelId));
				for (ChannelInfo ciInfo : channelList) {
					childrenIds.add(Integer.valueOf(ciInfo.getChannelId()));
				}
			}
			String channelParams = "";
			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (int num = 0; num < childrenIds.size(); num++) {
					channelParams = channelParams + ",?";
				}
				channelParams = channelParams.length() > 0 ? channelParams
						.substring(1) : "";
				sql = sql + " and rel.channelid_fk in (" + channelParams + ") ";
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}

			if ((childrenIds != null) && (childrenIds.size() > 0)) {
				for (Iterator<Integer> localIterator2 = childrenIds.iterator(); localIterator2
						.hasNext();) {
					int id = localIterator2.next(); 
					i++;
					pstmt.setInt(i, Integer.valueOf(id).intValue());
				}
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	private List<NewsDTO> getNewsDTOByRS(ResultSet rs) {
		List<NewsDTO> list = new ArrayList<NewsDTO>();
		NewsDTO dto = null;
		try {
			while (rs.next()) {
				dto = new NewsDTO();

				NewsInfo newsInfo = new NewsInfo();
				newsInfo.setNewsId(rs.getInt("news_id"));
				newsInfo.setLanguageId(rs.getInt("news_lgid_fk"));
				newsInfo.setTitle(rs.getString("news_title"));
				newsInfo.setTag(rs.getString("news_tag"));
				newsInfo.setAuthor(rs.getString("news_author"));
				newsInfo.setFrom(rs.getString("news_from"));
				newsInfo.setPublishTime(rs.getString("news_publishtime"));
				newsInfo.setTextType(rs.getInt("news_texttype"));
				newsInfo.setContent(rs.getString("news_content"));
				newsInfo.setIsFouceImage(rs.getInt("news_isfouceimage"));
				newsInfo.setMainImage(rs.getString("news_mainimage"));
				newsInfo.setKeywords(rs.getString("news_keywords"));
				newsInfo.setNewsMeta(rs.getString("news_newsmeta"));
				newsInfo.setNewsOrder(rs.getInt("news_order"));
				newsInfo.setUserId(rs.getInt("userid_fk"));
				newsInfo.setUserInfo(new UserInfoDao().getUser(newsInfo
						.getUserId()));
				dto.setNewsInfo(newsInfo);

				NewsChannelRel rel = new NewsChannelRel();
				rel.setId(rs.getInt("id"));
				rel.setIsDelete(rs.getInt("is_delete"));
				rel.setIsTop(rs.getInt("is_top"));
				rel.setTopTime(rs.getString("top_time"));
				rel.setIsShowInIndex(rs.getInt("is_showinindex"));
				rel.setIsShowInList(rs.getInt("is_showinlist"));
				rel.setIsReview(rs.getInt("is_review"));
				rel.setNewsId(rs.getInt("newsid_fk"));
				rel.setChannelId(rs.getInt("channelid_fk"));
				rel.setChannelnfo(new ChannelInfoDao()
						.getChannelByChannelId(rel.getChannelId()));
				dto.setRel(rel);

				List<ChannelInfo> channelList = getChannelListByNewsId(newsInfo.getNewsId());
				dto.setChannelList(channelList);

				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private List<NewsInfo> getNewsInfoByRS(ResultSet rs) {
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		NewsInfo newsInfo = null;
		try {
			while (rs.next()) {
				newsInfo = new NewsInfo();

				newsInfo.setNewsId(rs.getInt("news_id"));
				newsInfo.setLanguageId(rs.getInt("news_lgid_fk"));
				newsInfo.setTitle(rs.getString("news_title"));
				newsInfo.setTag(rs.getString("news_tag"));
				newsInfo.setAuthor(rs.getString("news_author"));
				newsInfo.setFrom(rs.getString("news_from"));
				newsInfo.setPublishTime(rs.getString("news_publishtime"));
				newsInfo.setTextType(rs.getInt("news_texttype"));
				newsInfo.setContent(rs.getString("news_content"));
				newsInfo.setIsFouceImage(rs.getInt("news_isfouceimage"));
				newsInfo.setMainImage(rs.getString("news_mainimage"));
				newsInfo.setKeywords(rs.getString("news_keywords"));
				newsInfo.setNewsMeta(rs.getString("news_newsmeta"));
				newsInfo.setNewsOrder(rs.getInt("news_order"));
				newsInfo.setUserId(rs.getInt("userid_fk"));
				newsInfo.setUserInfo(new UserInfoDao().getUser(newsInfo
						.getUserId()));

				List<ChannelInfo> channelList = getChannelListByNewsId(newsInfo.getNewsId());
				newsInfo.setChannelInfoList(channelList);

				list.add(newsInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getRelCountByNewsId(int newsId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select count(1) as count from tbl_news_channel_rel where newsid_fk = ?");
			pstmt.setInt(1, newsId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
			pstmt.close();
			rs.close();
			conn.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public boolean deleteNews(int newsId, int relId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			boolean isNeedDeleteNews = false;

			int relCount = getRelCountByNewsId(newsId);
			if (relCount <= 1) {
				isNeedDeleteNews = true;
			}

			PreparedStatement pstmtDelRel = conn
					.prepareStatement(" delete from  tbl_news_channel_rel  where id = ?");
			pstmtDelRel.setInt(1, relId);
			pstmtDelRel.execute();

			if (isNeedDeleteNews) {
				PreparedStatement pstmtDelNews = conn
						.prepareStatement("delete from tbl_news_i where news_id = ?");
				pstmtDelNews.setInt(1, newsId);
				pstmtDelNews.execute();
			}

			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean deleteNewsContentInfo(int newsId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmtDelNews = conn
					.prepareStatement("delete from tbl_news_i where news_id = ?");
			pstmtDelNews.setInt(1, newsId);
			pstmtDelNews.execute();
			pstmtDelNews.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean setNewsTopOrNot(int relId, int flag) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_news_channel_rel set is_top = ? , top_time = ?  where id = ?");
			pstmt.setInt(1, flag);
			pstmt.setString(2,
					DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
			pstmt.setInt(3, relId);
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean setPageShow(int relId, int flag, int pageIndex) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_news_channel_rel set "
							+ (pageIndex == 1 ? " is_showinindex = ? "
									: " is_showinlist = ? ") + " where id = ?");
			pstmt.setInt(1, flag);
			pstmt.setInt(2, relId);
			log.info(pstmt.toString());
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean setDestbin(int relId, int flag) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_news_channel_rel set is_delete = ? where id = ?");

			pstmt.setInt(1, flag);
			pstmt.setInt(2, relId);
			log.info(pstmt.toString());
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean setReview(int relId, int flag) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_news_channel_rel set is_review = ? where id = ?");
			pstmt.setInt(1, flag);
			pstmt.setInt(2, relId);
			pstmt.execute();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public NewsChannelRel getNewsRelById(int id) {
		NewsChannelRel rel = null;
		try {
			String selectSQL = "select * from tbl_news_channel_rel where id = ? ";
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(selectSQL);
			int i = 0;
			i++;
			pstmt.setInt(i, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				rel = new NewsChannelRel();
				rel.setId(rs.getInt("id"));
				rel.setIsDelete(rs.getInt("is_delete"));
				rel.setIsTop(rs.getInt("is_top"));
				rel.setTopTime(rs.getString("top_time"));
				rel.setIsShowInIndex(rs.getInt("is_showinindex"));
				rel.setIsShowInList(rs.getInt("is_showinlist"));
				rel.setIsReview(rs.getInt("is_review"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rel;
	}

	public NewsDTO getNewsDTOInfoById(int newsId, int relId) {
		NewsDTO dto = new NewsDTO();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_news_i news,tbl_news_channel_rel rel where news.news_id = rel.newsid_fk and news.news_id = ? and rel.id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setInt(i, newsId);
			i++;
			pstmt.setInt(i, relId);
			ResultSet rs = pstmt.executeQuery();
			List<NewsDTO> list = getNewsDTOByRS(rs);
			dto = (list != null && list.size() > 0)  ? list.get(0) : dto ;
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dto;
	}

	public NewsInfo getNewsInfoByNewsId(int newsId) {
		NewsInfo newsInfo = new NewsInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_news_i   where news_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setInt(i, newsId);
			ResultSet rs = pstmt.executeQuery();
			List<NewsInfo> list = getNewsInfoByRS(rs);
			if ((list != null) && (list.size() > 0)) {
				newsInfo = (NewsInfo) list.get(0);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return newsInfo;
	}

	public boolean update(NewsDTO dto) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			PreparedStatement pstmtUpNews = conn
					.prepareStatement(" update tbl_news_i set news_lgid_fk = ? ,news_title = ? ,news_tag = ? ,news_author = ? ,news_from = ? ,news_publishtime = ? ,  news_texttype = ? ,news_content = ?,news_isfouceimage = ?,news_mainimage = ?,news_keywords = ?,news_newsmeta = ?,userid_fk = ?  where news_id = ?");

			int i = 0;
			i++;
			pstmtUpNews.setInt(i, dto.getNewsInfo().getLanguageId());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getTitle());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getTag());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getAuthor());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getFrom());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getPublishTime());
			i++;
			pstmtUpNews.setInt(i, dto.getNewsInfo().getTextType());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getContent());

			i++;
			pstmtUpNews.setInt(i, dto.getNewsInfo().getIsFouceImage());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getMainImage());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getKeywords());
			i++;
			pstmtUpNews.setString(i, dto.getNewsInfo().getNewsMeta());
			i++;
			pstmtUpNews.setInt(i, dto.getNewsInfo().getUserId());
			i++;
			pstmtUpNews.setInt(i, dto.getNewsInfo().getNewsId());
			pstmtUpNews.executeUpdate();

			PreparedStatement pstmtDelRel = conn
					.prepareStatement(" delete from  tbl_news_channel_rel  where newsid_fk = ?");
			pstmtDelRel.setInt(1, dto.getNewsInfo().getNewsId());
			pstmtDelRel.execute();

			String relSQL = " insert into tbl_news_channel_rel(  newsid_fk,channelid_fk,is_delete,is_top,top_time,is_showinindex,is_showinlist,is_review)  values(?,?,?,?,?,?,?,?)";

			PreparedStatement pstmtInsertRel = conn.prepareStatement(relSQL);

			i = 0;
			for (String c : dto.getNewsInfo().getChannelIds().split(",")) {
				i++;
				pstmtInsertRel.setInt(i, dto.getNewsInfo().getNewsId());
				i++;
				pstmtInsertRel.setInt(i, Integer.valueOf(c).intValue());
				i++;
				pstmtInsertRel.setInt(i, 0);
				i++;
				pstmtInsertRel.setInt(i, dto.getRel().getIsTop());
				i++;
				pstmtInsertRel.setString(i, DateFormatUtils.format(new Date(),
						"yyyy-MM-dd HH:mm:ss"));
				i++;
				pstmtInsertRel.setInt(i, dto.getRel().getIsShowInIndex());
				i++;
				pstmtInsertRel.setInt(i, dto.getRel().getIsShowInList());
				i++;
				pstmtInsertRel.setInt(i, dto.getRel().getIsReview());
				i = 0;
				pstmtInsertRel.addBatch();
			}
			pstmtInsertRel.executeBatch();

			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Map<String, List<String>> insert(NewsDTO dto) {
		Map<String, List<String>> map = new HashMap<String, List<String>>();
		List<String> messageIn = new ArrayList<String>();
		List<String> messageUp = new ArrayList<String>();
		try {
			String insertSQL = "insert into tbl_news_i(news_lgid_fk,news_title,news_tag,news_author,news_from,news_publishtime,news_texttype,news_content,news_isfouceimage,news_mainimage,news_keywords,news_newsmeta,news_order,userid_fk) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			PreparedStatement pstmtInNews = conn.prepareStatement(insertSQL);
			int i = 0;
			i++;
			pstmtInNews.setInt(i, dto.getNewsInfo().getLanguageId());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getTitle());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getTag());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getAuthor());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getFrom());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getPublishTime());
			i++;
			pstmtInNews.setInt(i, dto.getNewsInfo().getTextType());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getContent());

			i++;
			pstmtInNews.setInt(i, dto.getNewsInfo().getIsFouceImage());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getMainImage());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getKeywords());
			i++;
			pstmtInNews.setString(i, dto.getNewsInfo().getNewsMeta());
			i++;
			pstmtInNews.setInt(i, 0);
			i++;
			pstmtInNews.setInt(i, dto.getNewsInfo().getUserId());

			pstmtInNews.execute();

			String lastIdSQL = "select last_insert_id() as lastId";
			int lastId = 0;
			PreparedStatement pstmtLastId = conn.prepareStatement(lastIdSQL);
			ResultSet rs = pstmtLastId.executeQuery();
			if (rs.next()) {
				lastId = rs.getInt("lastId");
			}

			String updateOrder = "update tbl_news_i set news_order = ? where news_id = ?";
			PreparedStatement pstmtOder = conn.prepareStatement(updateOrder);
			pstmtOder.setInt(1, lastId);
			pstmtOder.setInt(2, lastId);
			pstmtOder.executeUpdate();

			NewsInfo newsInfo = dto.getNewsInfo();
			newsInfo.setNewsId(lastId);
			dto.setNewsInfo(newsInfo);

			String[] channelIds = dto.getNewsInfo().getChannelIds().split(",");
			List<ChannelInfo> channInfoList = getChannelByIds(channelIds);

			String inSQL = "insert into tbl_news_channel_rel(newsid_fk,channelid_fk,is_delete,is_top,top_time,is_showinindex,is_showinlist,is_review) values(?,?,?,?,?,?,?,?)";

			String upSQL = "update tbl_news_channel_rel set newsid_fk = ?, is_top = ? ,top_time = ? ,is_showinindex = ? ,is_showinlist = ? ,is_review = ?  where channelid_fk = ? ";

			PreparedStatement pstmtIn = conn.prepareStatement(inSQL);
			PreparedStatement pstmtUp = conn.prepareStatement(upSQL);
			for (ChannelInfo channelInfo : channInfoList) {
				boolean isHasRel = hasNewsChannelRel(channelInfo.getChannelId());
				if ((channelInfo.getShowType() == 1) || (!isHasRel)) {
					pstmtIn.setInt(1, lastId);
					pstmtIn.setInt(2, channelInfo.getChannelId());
					pstmtIn.setInt(3, 0);
					pstmtIn.setInt(4, dto.getRel().getIsTop());
					pstmtIn.setString(5, DateFormatUtils.format(new Date(),
							"yyyy-MM-dd HH:mm:ss"));
					pstmtIn.setInt(6, dto.getRel().getIsShowInIndex());
					pstmtIn.setInt(7, dto.getRel().getIsShowInList());
					pstmtIn.setInt(8, dto.getRel().getIsReview());
					pstmtIn.addBatch();

					messageIn.add(channelInfo.getChannelName());
				} else {
					pstmtUp.setInt(1, lastId);
					pstmtUp.setInt(2, dto.getRel().getIsTop());
					pstmtUp.setString(3, DateFormatUtils.format(new Date(),
							"yyyy-MM-dd HH:mm:ss"));
					pstmtUp.setInt(4, dto.getRel().getIsShowInIndex());
					pstmtUp.setInt(5, dto.getRel().getIsShowInList());
					pstmtUp.setInt(6, dto.getRel().getIsReview());
					pstmtUp.setInt(7, channelInfo.getChannelId());

					pstmtUp.addBatch();
					messageUp.add(channelInfo.getChannelName());
				}
			}
			pstmtIn.executeBatch();
			pstmtUp.executeBatch();

			conn.commit();
			conn.close();
			map.put("1", messageIn);
			map.put("2", messageUp);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	private List<ChannelInfo> getChannelByIds(String[] channelIds) {
		ChannelInfoDao dao = new ChannelInfoDao();
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		ChannelInfo info = null;
		for (String channelId : channelIds) {
			if (StringUtils.isNotBlank(channelId)) {
				info = dao.getChannelByChannelId(Integer.valueOf(channelId)
						.intValue());
				list.add(info);
			}
		}
		return list;
	}

	private boolean hasNewsChannelRel(int channelId) {
		boolean hasRel = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count  from tbl_news_channel_rel where channelid_fk = ?  ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, channelId);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				hasRel = rs.getInt("count") > 0;
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hasRel;
	}

	public List<ChannelInfo> getChannelListByNewsId(int newsId) {
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select ch.* from tbl_channel_i ch, tbl_news_channel_rel rel where ch.ch_id = rel.channelid_fk and rel.newsid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			i++;
			pstmt.setInt(i, newsId);
			ResultSet rs = pstmt.executeQuery();
			list.addAll(new ChannelInfoDao().getChannelListByRS(rs));
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<NewsDTO> getNewsListByQueryChannelId(QueryNewsObject query,
			int channelId) {
		List<NewsDTO> list = new ArrayList<NewsDTO>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT * FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk and rel.channelid_fk = ?  ";

			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}

			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}
			sql = sql + " group by news.news_id ";

			String order = "";
			switch (query.getOrderBy()) {
			case 1:
				order = "news.news_order";
				break;
			default:
				order = "news.news_publishtime";
			}

			sql = sql + " order by " + order + " desc limit ? ,? ";

			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setInt(i, channelId);
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}
			i++;
			pstmt.setInt(i, query.getStart());
			i++;
			pstmt.setInt(i, query.getNum());
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list = getNewsDTOByRS(rs);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getNewsCountByQueryChannelId(QueryNewsObject query, int channelId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "SELECT count(distinct(news.news_id)) as count FROM tbl_news_i news, tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk and rel.channelid_fk = ? ";

			if (query.getLanguageId() > 0) {
				sql = sql + " and news.news_lgid_fk = ?";
			}
			if (query.getIsFouceImage() > -1) {
				sql = sql + " and news.news_isfouceimage = ?";
			}
			if (query.getIsDelete() > -1) {
				sql = sql + " and rel.is_delete = ?";
			}
			if (query.getIsTop() > -1) {
				sql = sql + " and rel.is_top = ?";
			}
			if (query.getIsShowInIndex() > -1) {
				sql = sql + " and rel.is_showinindex = ?";
			}
			if (query.getIsShowInList() > -1) {
				sql = sql + " and rel.is_showinlist = ?";
			}
			if (query.getIsReview() > -1) {
				sql = sql + " and rel.is_review = ?";
			}
			if (StringUtils.isNotBlank(query.getTag())) {
				sql = sql + " and news.news_tag like ?";
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				sql = sql
						+ " and (news.news_title like ? or news_keywords like ? or news_newsmeta like ? or news_content like ? )";
			}
			if (query.getUserId() > 0) {
				sql = sql + " and news.userid_fk = ?";
			}
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			i++;
			pstmt.setInt(i, channelId);
			if (query.getLanguageId() > 0) {
				i++;
				pstmt.setInt(i, query.getLanguageId());
			}
			if (query.getIsFouceImage() > -1) {
				i++;
				pstmt.setInt(i, query.getIsFouceImage());
			}
			if (query.getIsDelete() > -1) {
				i++;
				pstmt.setInt(i, query.getIsDelete());
			}
			if (query.getIsTop() > -1) {
				i++;
				pstmt.setInt(i, query.getIsTop());
			}
			if (query.getIsShowInIndex() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInIndex());
			}
			if (query.getIsShowInList() > -1) {
				i++;
				pstmt.setInt(i, query.getIsShowInList());
			}
			if (query.getIsReview() > -1) {
				i++;
				pstmt.setInt(i, query.getIsReview());
			}

			if (StringUtils.isNotBlank(query.getTag())) {
				i++;
				pstmt.setString(i, "%" + query.getTag() + "%");
			}
			if (StringUtils.isNotBlank(query.getKeywords())) {
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
				i++;
				pstmt.setString(i, "%" + query.getKeywords() + "%");
			}
			if (query.getUserId() > 0) {
				i++;
				pstmt.setInt(i, query.getUserId());
			}
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public NewsDTO getNewsDTOByChannelId(int channelId) {
		NewsDTO dto = new NewsDTO();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select * from tbl_news_i news,tbl_news_channel_rel rel where news.news_id=rel.newsid_fk and rel.channelid_fk = ? limit 0,1");
			pstmt.setInt(1, channelId);
			ResultSet rs = pstmt.executeQuery();
			List<NewsDTO> list = getNewsDTOByRS(rs);
			if ((list != null) && (list.size() > 0)) {
				dto = (NewsDTO) list.get(0);
			}
			conn.close();
			return dto;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dto;
	}

	public boolean sortNews(int newsId, int orderId, String flag,
			int newsChannelId) {
		newsChannelId = newsChannelId == 0 ? 1 : newsChannelId;
		boolean reslut = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pstmtItem = conn
					.prepareStatement("update tbl_news_i set news_order = ? where news_id = ?");
			PreparedStatement pstmtReplaceItem = conn
					.prepareStatement("update tbl_news_i set news_order = ? where news_id = ?");
			int replaceItemNewsId = 0;
			int replaceItemOrderId = 0;
			if (StringUtils.equals(flag, "up")) {
				String up = " select news.news_id,rel.newsid_fk,news.news_order,news.news_title  from tbl_news_i news,tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk and rel.channelid_fk = "
						+ newsChannelId
						+ " and news.news_order > "
						+ orderId
						+ " and rel.is_delete=0 order by news.news_order asc limit 0 ,1 ";

				Statement stmtUp = conn.createStatement();
				ResultSet rsUp = stmtUp.executeQuery(up);
				log.info("[newsInfoDao] #sortNews#" + orderId + " " + flag
						+ " " + stmtUp.toString());
				if (rsUp.next()) {
					replaceItemNewsId = rsUp.getInt("news.news_id");
					replaceItemOrderId = rsUp.getInt("news.news_order");
				}
			} else if (StringUtils.equals(flag, "down")) {
				String down = " select news.news_id,rel.newsid_fk,news.news_order,news.news_title  from tbl_news_i news,tbl_news_channel_rel rel  where news.news_id = rel.newsid_fk and rel.channelid_fk = "
						+ newsChannelId
						+ " and news.news_order < "
						+ orderId
						+ " and rel.is_delete=0 order by news.news_order desc limit 0 ,1 ";
				Statement stmtDown = conn.createStatement();
				ResultSet rsDown = stmtDown.executeQuery(down);
				log.info("[newsInfoDao] #sortNews#" + orderId + " " + flag
						+ " " + stmtDown.toString());
				if (rsDown.next()) {
					replaceItemNewsId = rsDown.getInt("news.news_id");
					replaceItemOrderId = rsDown.getInt("news.news_order");
				}
			} else {
				reslut = true;
			}
			if (replaceItemNewsId > 0) {
				pstmtItem.setInt(1, replaceItemOrderId);
				pstmtItem.setInt(2, newsId);
				pstmtItem.executeUpdate();
				log.info("[newsInfoDao] #sortNews# newsId " + newsId + "["
						+ orderId + " --> " + replaceItemOrderId + "] " + flag
						+ " " + pstmtItem.toString());

				pstmtReplaceItem.setInt(1, orderId);
				pstmtReplaceItem.setInt(2, replaceItemNewsId);
				pstmtReplaceItem.executeUpdate();
				log.info("[newsInfoDao] #sortNews# replaceItemNewsId "
						+ replaceItemNewsId + "[" + replaceItemOrderId
						+ " --> " + orderId + "] " + flag + " "
						+ pstmtReplaceItem.toString());
			}

			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return reslut;
	}

	public List<NewsInfo> rss(int channelId, int start, int num) {
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select news.news_id as newsId,news.news_title as title,news.news_publishTime as publishTime,news.news_content as content from tbl_news_i news,tbl_news_channel_rel rel where news.news_id=rel.newsid_fk and rel.channelid_fk = ? and rel.is_delete = 0 group by news.news_id order by news.news_publishtime desc limit ? ,?  ");

			pstmt.setInt(1, channelId);
			pstmt.setInt(2, start);
			pstmt.setInt(3, num);
			ResultSet rs = pstmt.executeQuery();
			NewsInfo newsInfo = null;
			while (rs.next()) {
				newsInfo = new NewsInfo();
				newsInfo.setNewsId(rs.getInt("newsId"));
				newsInfo.setTitle(rs.getString("title"));
				newsInfo.setPublishTime(rs.getString("publishTime"));
				newsInfo.setContent(rs.getString("content"));

				list.add(newsInfo);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {
		String[] channelIds = "".split(",");
		for (String channelId : channelIds)
			System.out.println(channelId);
	}
}